# -*- coding: utf-8 -*-

__access__ = 0

try: from mod_python import apache
except: pass
import os
import time as tm
from re import compile as re_compile
from os import path
import urllib2
import MySQLdb
from Cookie import SimpleCookie as Cookie
from random import randint as rnd
from time import time
import config
from md5 import md5
import urllib
import sys

times = {
"dbconnect": 0.0,
"dbquery": 0.0,
"dbclose": 0.0,
"querycount": 0
}

def time2str(t, rnd=False, lng='ru'):
 minutes, seconds = divmod(t, 60)
 hours, minutes = divmod(minutes, 60)
 days, hours = divmod(hours, 24)
 months, days = divmod(days, 30)
 if t<300: return u'сейчас'
 if rnd: r = u'сейчас'
 else: r = u'%0.2f %s' % (seconds, get_seconds(2, lng))
 if t>60: r = u'%d %s' % (minutes, get_minutes(round(minutes), lng))
 else: return r
 if t>3600: r = u'%d %s %s' % (hours, get_hours(round(hours), lng), r)
 if t>86400: r = u'%d %s %s' % (days, get_days(round(days), lng), r)
 if t>2592000: r = u'%d %s %s' % (months, get_months(round(months), lng), r)
 return r + u' назад'

def period2str(t):
 """
 переводит время в секундах в строковое представление в днях
 """
 t = t / 86400
 return str(t) + ' ' + get_days(t, u'ru')

def get_seconds(c, l):
 if l == u'ru':
  if c % 100 in xrange(10,20): return u'секунд'
  elif c % 10 == 1: return u'секунду'
  elif c % 10 in [2, 3, 4]: return u'секунды'
  else: return u'секунд'
 else:
  if c == 1: return u'second'
  else: return u'seconds'

def get_minutes(c, l):
 if l == u'ru':
  if c % 100 in xrange(10,20): return u'минут'
  elif c % 10 == 1: return u'минуту'
  elif c % 10 in [2, 3, 4]: return u'минуты'
  else: return u'минут'
 else:
  if c == 1: return u'minute'
  else: return u'minutes'

def get_hours(c, l):
 if l == u'ru':
  if c % 100 in xrange(10,20): return u'часов'
  elif c % 10 == 1: return u'час'
  elif c % 10 in [2, 3, 4]: return u'часа'
  else: return u'часов'
 else:
  if c == 1: return u'hour'
  else: return u'hours'

def get_days(c, l):
 if l == u'ru':
  if c % 100 in xrange(10,20): return u'дней'
  elif c % 10 == 1: return u'день'
  elif c % 10 in [2, 3, 4]: return u'дня'
  else: return u'дней'
 else:
  if c == 1: return u'day'
  else: return u'days'

def get_months(c, l):
 if l == u'ru':
  if c % 100 in xrange(10,20): return u'месяцев'
  elif c % 10 == 1: return u'месяц'
  elif c % 10 in [2, 3, 4]: return u'месяца'
  else: return u'месяцев'
 else:
  if c == 1: return u'month'
  else: return u'months'


enc = config.html_enc
clean = re_compile(u'\$\w+\$')
site = re_compile(u'^(http\:\/\/)?((\w+\.)+\w+)\/?$')
email = re_compile(u'^[a-z0-9._%+-]+@(?:[a-z0-9-]+\.)+[a-z]{2,4}$')

HTML_ESCAPE_CHARS = (('&', '&amp;'),
                    ('<', '&lt;'),
                    ('>', '&gt;'),
                    ('"', '&quot;'))

def escape(text, chars=HTML_ESCAPE_CHARS):
    "Escape a few XML special chars with XML entities."
    for s, h in chars:
        text = text.replace(s, h)
    return text

class mysql_db:
 def __init__(self):
  global times
  times = {
  "dbconnect": 0.0,
  "dbquery": 0.0,
  "dbclose": 0.0,
  "querycount": 0
  }
  st = time()
  self.conn = MySQLdb.connect(db=config.dbname, host=config.dbhost, user=config.dbuser, passwd=config.dbpass)
  self.c = self.conn.cursor()
  self.c.execute('set NAMES utf8')
  times['dbconnect'] += time()-st
 
 def query(self, request, params=()):
  st = time()
  request = request.encode(config.db_enc)
  params = [self.encode(p) for p in params]
  self.c.execute(request, tuple(params))
  times['dbquery'] += time()-st
  times['querycount'] += 1
  return self.c
 
 def commit(self):
  st = time()
  self.conn.commit()
  times['dbquery'] += time()-st
 
 def decode(self, s):
  if s.__class__ == str: return s.decode(config.db_enc, 'replace')
  else: return s
 
 def encode(self, s):
  try: return s.encode(config.db_enc, 'replace')
  except: return s
 
 def dfn(self, key, value):
  self.query('delete from options where vkey=%s', (key, ))
  if value: self.query('insert into options values (%s, %s)', (key, value))
  self.commit()
 
 def wtf(self, key):
  q = self.query('select vvalue from options where vkey=%s', (key, )).fetchone()[0]
  return q
 
 def close(self):
  st = time()
  self.conn.close()
  times['dbclose'] += time()-st

def db_integrity():
 userids = [i[0] for i in db.query('select ID from users').fetchall()]
 traffids = [i[0] for i in db.query('select ID from traffic_types').fetchall()]
 tariffids = [i[0] for i in db.query('select ID from tariffs').fetchall()]
 to = {}
 pp = {}
 for i in traffids:
  to[i] = {}
  pp[i] = {}
 for pair in db.query('select userid, traf_id from prepaid_packets').fetchall():
  userid, traf_id = pair
  pp[traf_id][userid] = 1
 for pair in db.query('select tariff_id, traf_id from tariff_options').fetchall():
  tariff_id, traf_id = pair
  to[traf_id][tariff_id] = 1
 for traf_id in traffids:
  for userid in userids:
   if not pp[traf_id].has_key(userid):
    db.query('insert into prepaid_packets(userid, `traf_id`) values(%s, %s)', (userid, traf_id))
  for tariff_id in tariffids:
   if not to[traf_id].has_key(tariff_id):
    db.query('insert into tariff_options (`tariff_id`, `traf_id`) values(%s, %s)', (tariff_id, traf_id))
 db.commit()

def hash_passwd(passwd):
 return md5(passwd).hexdigest()

def check_user(column, value):
  count = db.query(u'select * from users where '+column+'=%s limit 1', (value, )).rowcount
  return (count>0)

wdays = [u'Понедельник', u'Вторник', u'Среда', u'Четверг', u'Пятница',\
u'Суббота', u'Воскресенье']

def week_bounds(t=tm.time()):
 wd = tm.localtime(t)[6]
 wstart = tm.localtime(t - 86400 * wd)
 wend = tm.localtime(t + 86400 * (6 - wd))
 return (tm.strftime(u'%Y-%m-%d', wstart), tm.strftime(u'%Y-%m-%d', wend))

def use_template(req, template, **params):
 try:
  fp = file(config.lastupdatefile, 'r')
  _tm = fp.read()
  fp.close()
 except: _tm = 'unknown time'
 f = file(path.join(config.base_dir, 'data', 'templates', template))
 m = f.read().decode(enc)
 f.close()
 params['time'] = _tm
 params['weekday'] = wdays[tm.localtime()[6]]
 params['spenttime'] = '%0.3f' % (time() - req.request_time)
 for key in ['dbquery', 'dbconnect', 'dbclose']:
     params[key] = '%0.3f' % times[key]
 params['querycount'] = str(times['querycount'])
 for key in params.keys():
  try: m = m.replace(u'$'+key+'$', params[key])
  except: raise ValueError(key)
 m = clean.sub(u'', m).encode(enc)
 if template in ['lpanel.html', 'tpanel.html', 'main.html', 'trafficitem.html',\
 'detailitem.html', 'user.html', 'payment.html', \
 'tariff.html', 'info_item.html', 'detailitem-ip.html', 'detailitem-port.html', 'detailitem-user.html', 'detailbyuser.html']:
  return m
 else:
  if not params.has_key('submenu'):
   params['submenu'] = make_menu(req, 'tpanel.html', [[params['curstate'], None]])
  main = use_template(req, 'main.html', **params)
  main = main.replace('&PAGE&', m)
  return main

def make_menu(req, template, items):
 s = ''
 for pair in items:
  key, link = tuple(pair)
  if link: item, activity = '<A href="%s" target="_top">%s</A>' % (link, key), 'inact'
  else: item, activity = key, 'act'
  s += use_template(req, template, activity=activity, item=item)
 return s.decode(enc)

def url_exists(url):
 r = urllib2.Request(u'http://' + url + '/')
 r.add_header = ('User-agent', 'Mozilla/5.0')
 try: urllib2.urlopen(r)
 except: return False
 return True

def redirect(req, location):
 req.headers_out['location'] = location
 req.status = apache.HTTP_MOVED_TEMPORARILY
 return ''

def set_cookie_id(req, ID):
 # generate unique key..
 key = str(rnd(0, 9999999999)) + str(rnd(0, 9999999999))
 date = str(int(time()))
 # store unique key in database
 db.query('insert into cookies values (%s, %s, %s)', (date, str(ID), key))
 # clean from old cookies (more than 24 hours old)
 db.query('delete from cookies where date<%s', (str(int(time())-86400), ))
 db.commit()
 req.headers_out["Set-Cookie"] = 'nauthskey=%sx%s; Max-Age=86400' % (str(ID), key)

def get_cookie_id(req):
 h = req.headers_in
 if 'Cookie' in h.keys():
  c = Cookie()
  c.load(h['Cookie'])
  try: ID, key = c['nauthskey'].value.split('x')
  except: return 0
  #check key in database..
  if db.query('select * from cookies where skey=%s', (key, )).rowcount == 1:
   #key is valid
   return int(ID)
  else: return 0

def login(req, data):
 login = data.get('MailSign', '').lower().strip().decode(enc)
 passwd = hash_passwd(data.get('PassSign', '')).decode(enc)
 c = db.query('select ID from users where ((MAIL=%s) or (login=%s)) and PASS=%s', (login, login, passwd))
 if c.rowcount == 1: return int(c.fetchone()[0])
 else: return 0

globmenu = [[u'Статистика', 'stat'], [u'Платежи', 'payments'], [u'Список пользователей', 'users'], \
[u'Сменить пароль', 'chpass'], [u'Справка', 'tariff'], [u'Выход', 'logout']]

def gmenu():
 return [i[:] for i in globmenu]

def traffic(n):
 n = float(n)
 if n is None: n = 0
 if n < 1024: return str(int(n)) + u' Б'
 n = n / 1024
 if n < 1024: return ('%0.2f' % (n, )) + u' кБ'
 n = n / 1024
 if n < 1024: return ('%0.2f' % (n, )) + u' МБ'
 n = n / 1024
 return ('%0.2f' % (n, )) + u' ГБ'

months = [u'', u'январь', u'февраль', u'март', u'апрель', u'май', u'июнь', u'июль', u'август', u'сентябрь', u'октябрь', \
u'ноябрь', u'декабрь']

def money_str(x):
 """
 переводит float в строковую денежную запись
 к примеру 213.0421 => "213.04 грн."
 """
 if x > 0.995: return u'%0.2f грн.' % (x, )
 elif x > 0.0095: return u'%0.0f коп.' % (100*x, )
 elif abs(x) > 0.0001: return u'%0.2f коп.' % (100*x, )
 else: return u'0 коп.'

def calculate(n):
 """
 функция получает траффик в байтах, возвращает
 текстовое описание цены в грн., из расчёта 1 коп/мб.
 """
 s = float(n)/(1024*1024*100)
 return money_str(s)

def detail_link(req, year, month, day, localtraf):
 pass

def inet_status(st, dt):
 if st == 0: return u'Выключен'
 else: return u'Включён до %s' % tm.strftime("%Y-%m-%d %H:%M:%S", tm.localtime(dt))

def lastact(la):
 if la == 0: return u'<font color="grey">никогда</font>'
 else: 
  la = round(tm.time()-la)
  if la<300: return u'<font color="green">сейчас</font>'
  else: return '<font color="black">'+time2str(la, True)+'</font>'

def mytime(t):
 return tm.strftime('%Y-%m-%d %H:%M:%S', tm.localtime(t))

def show_stat(req, user, msg='', mode='info', traffic_type='1', year=tm.strftime('%Y'), month=None, day=None):
 try: globmsg = db.decode(db.wtf('globmsg'))
 except: globmsg = ''
 url = 'stat?'
 if year: url += 'year='+year+'&'
 if month: url += 'month='+month+'&'
 if day: url += 'day='+day+'&'
 traffic_type = str(int(traffic_type))
 menu = gmenu()
 menu[0][1] = None
 mainmenu = make_menu(req, 'lpanel.html', menu)
 submenu = [[u'Общая информация', url+'mode=info&traffic_type='+traffic_type], \
 [u'Отчёты по трафику', url+'mode=traffic&traffic_type='+traffic_type], \
 [u'Детализация', url+'mode=detail&traffic_type='+traffic_type]]
 curtraffic_type = db.query('select description from traffic_types where ID=%s', \
 (traffic_type, )).fetchall()[0][0].decode('utf8')
 tlist = db.query('select ID, description from traffic_types where ID<>%s and public>0 order by ID',\
 (traffic_type, ))
 othertraffic_types = u' / '.join([u'<a href="%smode=%s&traffic_type=%s">%s</a>' % \
 (url, mode, ID, desc.decode('utf8')) for ID, desc in tlist.fetchall()])
 if mode == 'info':
  submenu[0][1] = None
  submenu = make_menu(req, 'tpanel.html', submenu)
  status = inet_status(user.status, user.netuntil)
  invert = [u'Включить', u'Выключить'][user.status]
  acct = [u'on', 'off'][user.status]
  if user.status: ic = 'green'
  else: ic = 'red'
  if user.balance >= 0: bc = 'green'
  else: bc = 'red'
  balance = '%0.2f' % (user.balance, )
  wstart, wend = week_bounds(tm.time())
  pwstart, pwend = week_bounds(tm.time() - 86400*7)
  credit = '%0.2f' % (user.credit, )
  pp = db.query('select traffic_types.description, prepaid from traffic_types, prepaid_packets'+\
  ' where userid=%s and traffic_types.ID=prepaid_packets.traf_id and traffic_types.public>0', (user.ID, ))
  prepaid = u''.join([use_template(req, 'info_item.html', variable=u'Предоплачено трафика "%s"' % \
  (desc.decode('utf8'), ),  value=traffic(packet)).decode('utf8') for desc, packet in pp])
  oc = db.query('select traffic_types.description, cost from traffic_types, tariff_options, users'+\
  ' where users.ID=%s and traffic_types.ID=tariff_options.traf_id and traffic_types.public>0 and '+\
  ' users.TARIF=tariff_options.tariff_id', (user.ID, ))
  overcosts = u''.join([use_template(req, 'info_item.html', variable=u'Стоимость превышения за 1мб "%s"' % \
  (desc.decode('utf8'), ),  value=money_str(cost)).decode('utf8') for desc, cost in pp])
  t1, t2, price = db.query('select users.lastabon, users.lastabon+tariffs.interval, price '+\
  'from users, tariffs where users.id=%s and users.tarif=tariffs.id', \
  (user.ID, )).fetchone()
  if price > 0.01:
   tarif = u'%s (оплачено с %s до %s)' % (user.starif, mytime(t1), mytime(t2))
  else: tarif = user.starif
  return use_template(req, 'info.html', balance=balance, status=status,\
  invert=invert, login=user.login, mail=user.mail, ip=user.ip, remoteip=user.remote_ip,\
  time=tm.strftime('%H:%M:%S'), bc=bc, ic=ic, msg=msg.decode(enc), menu=mainmenu,\
  submenu=submenu, acct=acct, uid=str(user.ID), curstate=u'Общая информация',\
  regdate=user.regdate, comment=user.comment,\
  lastact=lastact(user.lastact), tarif=tarif, credit=credit, prepaid=prepaid,
  overcosts=overcosts, apikey=user.apikey, globalmsg=globmsg)
 elif mode == 'traffic':
  submenu[1][1] = None
  submenu = make_menu(req, 'tpanel.html', submenu)
  if month:
   if day:
    date = '%s-%s-%s' % (year, month, day)
    interval = date
    segm = u'час'
    if user.is_admin():
     data = db.query('select hour, sum(bytes_in), sum(bytes_out), sum(cost) from'+\
     ' trafficlog where date=%s and traffic_type=%s group by hour order by hour', (date, traffic_type))
    else:
     data = db.query('select hour, sum(bytes_in), sum(bytes_out), sum(cost) from'+\
     ' trafficlog where date=%s and traffic_type=%s and userid=%s group by hour order by hour', \
     (date, traffic_type, user.ID))
    data = data.fetchall()
    tbl = ''.join([use_template(req, 'trafficitem.html', segment=str(segment)+':00 - '+str(int(segment)+1)+':00',\
    trafficin=traffic(t_in),trafficout=traffic(t_out), traffictotal=traffic(t_in+t_out),\
    topay=money_str(cost)) for segment, t_in, t_out, cost in data])
   else:
    interval = u'%s %s года' % (months[int(month)], year)
    segm = u'День'
    #mdate = '%s-%s-%%' % (year, month)
    if user.is_admin():
     data = db.query('select DAY(date) as day, sum(bytes_in), sum(bytes_out), '+\
     'sum(cost) from trafficlog where YEAR(date)=%s and MONTH(date)=%s and traffic_type=%s group by day order by day', \
     (year, month, traffic_type))
    else:
     data = db.query('select DAY(date) as day, sum(bytes_in), sum(bytes_out), '+\
     'sum(cost) from trafficlog where YEAR(date)=%s and MONTH(date)=%s and traffic_type=%s and '+\
     'userid=%s group by day order by day',\
     (year, month, traffic_type, user.ID))
    data = data.fetchall()
    tbl = ''.join([use_template(req, 'trafficitem.html', segment=\
    '<a href="%sday=%s&mode=traffic&traffic_type=%s">%s</a>' % (url, day, traffic_type, day),\
    trafficin=traffic(t_in), trafficout=traffic(t_out), traffictotal=traffic(t_in+t_out),\
    topay=money_str(cost)) for day, t_in, t_out, cost in data])
  else:
   interval = year + u' год'
   segm = u'Месяц'
   #mdate = '%s-%%' % (year, )
   if user.is_admin():
    data = db.query('select MONTH(date) as month, sum(bytes_in), sum(bytes_out), '+\
    'sum(cost) from trafficlog where YEAR(date)=%s and traffic_type=%s group by month order by month', \
    (year, traffic_type))
   else:
    data = db.query('select MONTH(date) as month, sum(bytes_in), sum(bytes_out), '+\
    'sum(cost) from trafficlog where YEAR(date)=%s and traffic_type=%s and userid=%s group by month order by month',\
    (year, traffic_type, user.ID))
   data = data.fetchall()
   tbl = ''.join([use_template(req, 'trafficitem.html', segment=\
   '<a href="%smonth=%s&mode=traffic&traffic_type=%s">%s</a>' % (url, month, traffic_type, months[int(month)]),\
   trafficin=traffic(t_in), trafficout=traffic(t_out), traffictotal=traffic(t_in+t_out),\
   topay=money_str(cost)) for month, t_in, t_out, cost in data])
  return use_template(req, 'traffic.html', ip=user.ip, interval=interval, \
  segments=segm, trafficitems=tbl.decode(enc), menu=mainmenu, submenu=submenu, curstate=u'Отчёты по трафику',\
  curtraffic_type=curtraffic_type, othertraffic_types=othertraffic_types)
 elif mode == 'detail':
  submenu[2][1] = None
  submenu = make_menu(req, 'tpanel.html', submenu)
  if month and len(month)<2: month = '0' + month
  if day and len(day)<2: day = '0' + day
  if month:
   if day: datemask = '%s-%s-%s' % (year, month, day)
   else: datemask = '%s-%s-%%' % (year, month)
  else: datemask = year + '-%'

  # users
  if user.is_admin():
   q = db.query("""select users.login as user, sum(bytes_in), sum(bytes_out), sum(bytes_in+bytes_out)
   as total, sum(cost) from trafficlog, users where traffic_type=%s and date like %s and userid=users.ID group
   by user order by total desc limit 50""", (traffic_type, datemask))
   detailitemsuser = u''.join(use_template(req, 'detailitem-user.html', username=login, \
   trafficin=traffic(t_in), trafficout=traffic(t_out),\
   traffictotal=traffic(t_all), topay=money_str(cost)).decode(enc) for login, t_in, t_out, t_all, cost in q)
   detailbyuser = use_template(req, 'detailbyuser.html', users=detailitemsuser).decode(enc)
  else:
   detailbyuser = u''

  # топ 10 IP
  if user.is_admin():
   q = db.query("""select concat(ip, ':', port), sum(bytes_in), sum(bytes_out), sum(bytes_in+bytes_out) 
   as total, sum(cost) from trafficdetail where traffic_type=%s and date like %s group 
   by concat(ip, ':', port) order by total desc limit 10""", (traffic_type, datemask))
  else:
   q = db.query("""select concat(ip, ':', port), sum(bytes_in), sum(bytes_out), sum(bytes_in+bytes_out) 
   as total, sum(cost) from trafficdetail where traffic_type=%s and date like %s and userid=%s 
   group by concat(ip, ':', port) order by total desc limit 10""", (traffic_type, datemask, user.ID))
  detailitemsip = u''.join(use_template(req, 'detailitem-ip.html', ip=ip, \
  trafficin=traffic(t_in), trafficout=traffic(t_out),\
  traffictotal=traffic(t_all), topay=money_str(cost)).decode('utf8') for ip, t_in, t_out, t_all, cost in q)
  
  # топ 10 портов
  if user.is_admin():
   q = db.query("""select port, sum(bytes_in), sum(bytes_out), sum(bytes_in+bytes_out) 
   as total, sum(cost) from trafficdetail where traffic_type=%s and date like %s group
   by port order by total desc limit 10""", (traffic_type, datemask))
  else:
   q = db.query("""select port, sum(bytes_in), sum(bytes_out), sum(bytes_in+bytes_out) 
   as total, sum(cost) from trafficdetail where traffic_type=%s and date like %s and userid=%s 
   group by port order by total desc limit 10""", (traffic_type, datemask, user.ID))
  detailitemsport = u''.join(use_template(req, 'detailitem-port.html', port=str(port), \
  trafficin=traffic(t_in), trafficout=traffic(t_out),\
  traffictotal=traffic(t_all), topay=money_str(cost)).decode('utf8') for port, t_in, t_out, t_all, cost in q)

  return use_template(req, 'detail.html', ip=user.ip, interval=datemask.replace('-%', ''), \
  menu=mainmenu, submenu=submenu, curstate=u'Детализация трафика', curtraffic_type=curtraffic_type,\
  othertraffic_types=othertraffic_types, detailitemsip=detailitemsip, detailitemsport=detailitemsport, \
  detailbyuser=detailbyuser)
 else: return 'Invalid mode!'

def tariffs(req):
 menu = gmenu()
 menu[4][1] = None
 menu = make_menu(req, 'lpanel.html', menu)
 submenu = make_menu(req, 'tpanel.html', [[u'Тарифы', None], [u'Настройка интернет', 'help']])
 trafflistr = db.query('select description from traffic_types where public>0 order by ID').fetchall()
 trafflist = u' / '.join([i[0].decode('utf8') for i in trafflistr])
 tariffs = u''
 opt = db.query('SELECT packet, cost from tariff_options, tariffs, traffic_types where '+\
 ' tariffs.id=tariff_options.tariff_id and traffic_types.id=tariff_options.traf_id and '+\
 'traffic_types.public>0 and tariffs.public>0 order by tariff_id, traf_id').fetchall()
 optc = 0
 for tarif in db.query('select description, price, tariffs.interval from tariffs where public>0 '+\
 'order by ID').fetchall():
  prepaid = []
  price = []
  for i in trafflistr:
   prepaid.append(opt[optc][0])
   price.append(opt[optc][1])
   optc += 1
  prepaid = ' / '.join([traffic(i) for i in prepaid])
  price = ' / '.join([money_str(i) for i in price])
  tariffs += use_template(req, 'tariff.html', tname=tarif[0], tabon=money_str(tarif[1]),\
  interval=period2str(tarif[2]), prepaid=prepaid, price=price).decode('utf8')
 return use_template(req, 'bonuses.html', menu=menu, curstate=u'Тарифы', submenu=submenu, \
 tariffs=tariffs, trafflist=trafflist)

def pay(uid, from_ID, sum, comment):
 if sum == 0: return
 db.query('insert into payments values(%s, CURDATE(), CURTIME(), %s, %s, %s)', \
 (str(uid), str(sum), str(from_ID), comment))
 db.query('update users set BALANCE=BALANCE+%s where ID=%s', (str(sum), str(uid)))
 db.commit()

def chtarif(uid, tarif):
 oldprice = db.query("""select price/tariffs.interval from tariffs, users where 
 tariffs.ID=users.TARIF and users.ID=%s""", (uid, )).fetchone()[0]
 newprice = db.query("""select price/tariffs.interval from tariffs where
 tariffs.ID=%s""", (tarif, )).fetchone()[0]
 if newprice < oldprice:
  # переход на более дешёвый тариф, обнуление пакетов
  db.query('update prepaid_packets set prepaid=0 where userid=%s', (uid, ))
 # если тариф изменился, то обнуляем дату последней абонплаты
 db.query('update users set TARIF=%s, lastabon=0 where ID=%s and TARIF<>%s', \
 (tarif, uid, tarif))
 update_tariffs(db)
 update_firewall(db)
 db.commit()

#def calc_bonus(bytes):
 #mb = bytes / (1024*1024)
 #if mb < 1024:
  #sum = 0
  #comment = u'нет бонуса'
 #else:
  #sum = mb*2000
  #comment = u'Бонус (за предыдущий день скачано более 1 Гб)'
 #return sum, comment

def update():
 global db
 db = mysql_db()
 db_integrity()
 db.query('delete from traffic_tmp')
 # парсим лог
 tlog = file(config.tlogfile, 'r')
 t = tlog.read().splitlines()
 tlog.close()
 #print '%s lines' % (len(t), )
 # некоторая подготовка перед обработкой лога и заполнением traffic_tmp
 # u - соответствие ID пользователей с их IP
 u = db.query('select users.ID, users.IP from users, tariffs where users.TARIF=tariffs.id')
 u_id_ip = u.fetchall()
 # список внутренних IP
 userips = [i[1] for i in u_id_ip]
 # для каждого пользователя и типа траффика узнаём, 
 # какой объём предоплачен, и сколько стоит превышение
 q = db.query('SELECT users.id, tariff_options.traf_id, prepaid_packets.prepaid, tariff_options.cost'+\
  ' FROM users, tariff_options, prepaid_packets '+\
  ' WHERE tariff_options.tariff_id = users.TARIF'+\
  ' AND users.ID = prepaid_packets.userid '+\
  ' AND tariff_options.traf_id = prepaid_packets.traf_id').fetchall()
 # предоплаченные пакеты
 prepaids = {}
 # стоимость превышения
 overcosts = {}
 # заполняем их..
 for line in q:
  userid, traf_id, prepaid, overcost = line
  prepaids.setdefault(userid, {})
  overcosts.setdefault(userid, {})
  prepaids[userid][traf_id] = prepaid
  overcosts[userid][traf_id] = overcost
 # тут мы записываем, что мы изменили в prepaids
 # множество пар (userid, traf_id)
 changed = set()
 j = 0
 for st in t:
  j += 1
  if j%10 == 0:
      sys.stdout.write('.')
      sys.stdout.flush()
  if j%1000 == 0: sys.stdout.write(str(j))
  if st:
   try: timestamp, prefix, iface_in, iface_out, from_ip, from_port, to_ip, \
   to_port, protocol, bytes, paketov = st.split()
   except: sys.stderr.write('<%s> is not valid line\n' % (st, ))
   
   # в prefix нам передаётся тип трафика, так что:
   traffic_type = int(prefix)
   bytes = int(bytes)
   bytes_in = 0
   bytes_out = 0
   
   if from_ip in userips:
    if to_ip in userips:
     sys.stderr.write('o_O <%s> is local-to-local\n', (st, ))
     continue
    else:
     # исходящий траффик
     userip = from_ip
     bytes_out = bytes
     ip = to_ip
     port = to_port
   else:
    if to_ip in userips:
     # входящий траффик
     userip = to_ip
     bytes_in = bytes
     ip = from_ip
     port = from_port
    else:
     sys.stderr.write('o_O <%s> - routing external-to-external\n' % (st, ))
     continue
   userid, userip = [i for i in u_id_ip if i[1] == userip][0]
   
   # вычисляем cost на основании таблички тарифов
   if prepaids[userid][traffic_type] > bytes:
    # есть предоплаченный траффик этого типа и его хватит
    # запоминаем...
    changed.add((userid, traffic_type))
    # ... что мы меняем prepaids для этой пары
    prepaids[userid][traffic_type] -= bytes
    # и не снимаем за него денег
    cost = 0
   else:
    # иначе предоплаченного траффика нет или слишком мало
    # снимаем деньги
    cost = float(overcosts[userid][traffic_type]) * bytes / 1048576
   
   db.query('insert into traffic_tmp(date, time, userid, ip, port, bytes_in, bytes_out, traffic_type, cost) values(' + \
   '%s, %s, %s, %s, %s, %s, %s, %s, %s)', (tm.strftime('%Y-%m-%d'), tm.strftime('%H:%M:00'), str(userid), ip,\
   str(port), str(bytes_in), str(bytes_out), traffic_type, str(cost)))
 
 ############ парсинг лога и заполнение traffic_tmp завершено #########
 
 print ' done'
 
 # теперь смотрим, у кого потратился (возможно частично) предплаченный траффик
 # и сохраняем изменения в БД
 for userid, traf_id in changed:
  prepaid = prepaids[userid][traf_id]
  db.query('update prepaid_packets set prepaid=%s where userid=%s and traf_id=%s', (prepaid, userid, traf_id))
 
 print "%s prepaid packets changed." % (len(changed), )
 
 ### updating balances ###
 u = db.query('SELECT userid, sum(cost) FROM traffic_tmp GROUP BY userid').fetchall()
 for user in u:
  userid, cost = user
  db.query('update users set BALANCE=BALANCE-%s, LASTACT=UNIX_TIMESTAMP() '+\
  'where ID=%s', (str(cost), str(userid)))
 print '%s user balances updated.' % (len(u))
 ### /updating balances ##

 # обновляем тарифы
 update_tariffs(db)
 #  обновляем файрволл
 update_firewall(db)
 
 # переносим данные из временной таблицы в основную
 db.query('INSERT INTO traffic (date, time, userid, ip, port, bytes_in, bytes_out, traffic_type, cost) '+\
 'SELECT date, time, userid, ip, port, sum(bytes_in) as bytes_in, sum(bytes_out) as bytes_out, '+\
 'traffic_type, sum(cost) as cost FROM traffic_tmp GROUP BY date, time, userid, ip, port, traffic_type')

 # а также в отптимизированные для показа статистики
 db.query("insert into trafficlog(date, hour, userid, bytes_in, bytes_out, traffic_type, cost) select date, HOUR(time)"+\
 " as hour, userid, sum(bytes_in), sum(bytes_out), traffic_type, sum(cost) from traffic_tmp group by date, hour, userid, "+\
 " traffic_type")

 # и для детализации по IP
 db.query("insert into trafficdetail(date, userid, ip, port, bytes_in, bytes_out, traffic_type, cost) select date, userid, ip, port, sum(bytes_in), sum(bytes_out), traffic_type, sum(cost) from traffic_tmp group by date, userid, ip, port, traffic_type")
 
 # очищаем временную таблицу
 db.query('delete from traffic_tmp')
 db.commit()
 db.close()

class user:
 def __init__(self, req, ID):
  self.req = req
  c = db.query('select users.ID,login,MAIL,PASS,BALANCE,IP,STATUS,REGDATE,COMMENT,'+\
  'NETUNTIL,LASTACT,TARIF,LASTABON,description,credit,sha(concat(users.login, users.PASS)) from users,tariffs '+\
  'where users.id=%s and users.tarif=tariffs.id', (str(ID), ))
  info = [db.decode(i) for i in c.fetchone()]
  self.ID, self.login, self.mail, self.passwd, self.balance, self.ip, self.status, \
  self.regdate, self.comment, self.netuntil, self.lastact, \
  self.tarif, self.lastabon, self.starif, self.credit, self.apikey = tuple(info)
  self.regdate = str(self.regdate)
  self.balance = float(self.balance)
  try: self.remote_ip = req.get_remote_host()
  except: self.remoteip = '0.0.0.0'
  self.ID = ID
 def is_admin(self):
  return self.mail in config.admins
 def inet(self, state, f):
  db.query('update users set STATUS=%s, NETUNTIL=UNIX_TIMESTAMP()+%s where ID=%s and BALANCE+credit>=0', (state, f, str(self.ID)))
  self.status = int(state)
  db.commit()
  if (state == '1') and (self.remote_ip <> self.ip):
   msg = u'<br/>\nСейчас ваш IP %s, для корректной работы надо поменять его на зарегистрированный в системе: %s' % \
   (self.remote_ip, self.ip)
  else: msg = ''
  #msg = u'Запрос принят и будет обработан в течение пяти минут.' + msg
  return redirect(self.req, 'stat?'+urllib.urlencode([('msg', msg.encode(enc))]))

def valid_login(login):
 import re
 return re.match('^[a-z][a-z\-\_0-9]{2,19}$', login)

def iplist():
     global db
     db = mysql_db()
     q = db.query('select IP from users where STATUS=1')
     q = [x[0] for x in q.fetchall()]
     db.close()
     return q

def update_firewall(db):
    # switching internet statuses
    db.query('update users set STATUS=0 where NETUNTIL<UNIX_TIMESTAMP()')
    #db.query('update users set STATUS=1 where BALANCE+credit>=0') # will be removed
    db.query('update users set STATUS=0 where BALANCE+credit<0')
    db.query('update users, tariffs set STATUS=0 where users.tarif=tariffs.id and tariffs.typ=1')
    os.popen('sudo /usr/sbin/nstat-provide')

def update_tariffs(db):
 ### tariffs ####################
 # abonent payments
 u = db.query('select users.id, users.TARIF, -tariffs.price, tariffs.description from '+\
 'users, tariffs where (users.tarif=tariffs.id) and '+\
 '(users.lastabon+tariffs.interval<UNIX_TIMESTAMP()) '+\
 'and (users.balance+users.credit>=0)').fetchall()
 if u:
  for userid, tarif_id, price, tarif in u:
   pay(userid, 0, price, 'Abonent payment: ' + tarif)
   db.query('update users set lastabon=UNIX_TIMESTAMP() where id=%s', (userid, ))
   packets = db.query("""select traf_id, packet from tariff_options where tariff_id=%s""", \
   (str(tarif_id), )).fetchall()
   for traf_id, packet in packets:
    db.query("""update prepaid_packets set prepaid=prepaid+%s where userid=%s and traf_id=%s""", \
    (str(packet), str(userid), str(traf_id)))
